package com.thh.datamonitor.executor.dao;

import com.thh.common.exception.sql.SqlException;
import com.thh.common.utils.StringUtils;
import com.thh.datamonitor.dto.TmdTableDto;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * MySQL
 *
 */
@Repository
public class MySQLTableDao implements TableInfoDao {

    @Override
    public List<TmdTableDto> queryList(Connection conn,String dbname, String tableName) {

        String sql = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables" +
                " where table_schema = ?  ";
        if (StringUtils.isNotBlank(tableName)) {
            sql += " and table_name like  concat('%', ?, '%') ";
        }
        sql += "order by create_time desc";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TmdTableDto> tableInfoListDto = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, dbname);
            if (StringUtils.isNotBlank(tableName)) {
                pstmt.setString(2, tableName);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                TmdTableDto tmdTableDto = new TmdTableDto();
                tmdTableDto.setTableName(rs.getString("tableName"));
                tmdTableDto.setEngine(rs.getString("engine"));
                tmdTableDto.setTableComment(rs.getString("tableComment"));
                tmdTableDto.setCreateTime(rs.getDate("createTime"));
                tableInfoListDto.add(tmdTableDto);
            }
        } catch (SQLException e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
        return tableInfoListDto;
    }

    @Override
    public TmdTableDto selectOne(Connection conn, String dbname, String tableName)  {
        String sql = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables" +
                " where table_schema = ? and table_name =   ? ";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        TmdTableDto tmdTableDto = new TmdTableDto();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, dbname);
            pstmt.setString(2, tableName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tmdTableDto.setTableName(rs.getString("tableName"));
                tmdTableDto.setEngine(rs.getString("engine"));
                tmdTableDto.setTableComment(rs.getString("tableComment"));
                tmdTableDto.setCreateTime(rs.getDate("createTime"));
            }
        } catch (SQLException e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
        return tmdTableDto;
    }

    @Override
    public List<Map<String, String>> queryColumns(String tableName) {
        return null;
    }

    @Override
    public String ddl(Connection conn,  String dbName, String tableName) {
        String sql = "SHOW CREATE TABLE " + tableName;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            String table = rs.getString("Table");
            String createTable = rs.getString("Create Table");
            return createTable;
        } catch (Exception e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
    }

    @Override
    public String getPrimaryKey(Connection connection, String dbname, String tableName)  {
        String finalSql = "SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_schema = ? AND table_name = ? AND constraint_name='PRIMARY'";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(finalSql);
            pstmt.setString(1, dbname);
            pstmt.setString(2, tableName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
        return null;
    }
}
